package wsp.dailymarket.deals;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.annotation.Resource;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.ArrayDataModel;
import javax.faces.model.DataModel;
import javax.sql.DataSource;
import wsp.dailymarket.shopping.InvoiceDealCode;

/**
 * Represents a code list
 * @author Matt
 */
@ManagedBean(name="codeListBean")
@RequestScoped
public class CodeListBean {

	@Resource(name = "jdbc/dmderby")
	private DataSource ds;
	@ManagedProperty(value = "#{dealList}")
	private DealListBean dealListBean;
	private ArrayList<InvoiceDealCode> codeList = new ArrayList<InvoiceDealCode>();

	public int getPurchasesOfSingDeal() {
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "SELECT "
						+ "COUNT(APP.DEAL_CODES.ID) as order_count "
						+ "FROM APP.DEAL_CODES "
						+ "LEFT OUTER JOIN APP.DEAL_ORDERS "
						+ "ON APP.DEAL_ORDERS.ID = APP.DEAL_CODES.DEAL_ORDER_ID "
						+ "WHERE APP.DEAL_ORDERS.DEAL_ID = " + dealListBean.getDealBean().getID();
				ResultSet rs = stmt.executeQuery(sql);
				if (rs.next()) {
					return rs.getInt("order_count");
				} else {
					return 0;
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error");
			FacesContext.getCurrentInstance().addMessage(null, msg);
			return 0;
		}
	}

	public boolean getAllDealCodesRendered() {
		codeList.clear();
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "SELECT "
						+ "APP.DEAL_CODES.*, "
						+ "(SELECT CLIENT_ID FROM APP.ORDERS WHERE APP.ORDERS.ID = "
						+ "(SELECT APP.DEAL_ORDERS.ORDER_ID FROM APP.DEAL_ORDERS WHERE APP.DEAL_ORDERS.ID = APP.DEAL_CODES.DEAL_ORDER_ID)) as CUSTOMER "
						+ "FROM APP.DEAL_CODES "
						+ "INNER JOIN APP.DEAL_ORDERS "
						+ "ON APP.DEAL_ORDERS.ID = APP.DEAL_CODES.DEAL_ORDER_ID "
						+ "WHERE APP.DEAL_ORDERS.DEAL_ID = "
						+ dealListBean.getDealBean().getID();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					InvoiceDealCode newCode = new InvoiceDealCode();
					newCode.populate(
							rs.getInt("ID"),
							rs.getString("CODE_DATA"),
							rs.getFloat("PRICE_POINT"),
							rs.getInt("CODE_USED") == 1,
							rs.getString("CUSTOMER"));
					codeList.add(newCode);
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error");
			FacesContext.getCurrentInstance().addMessage(null, msg);
		}
		return this.getHasCodes();
	}

	public boolean getHasCodes() {
		return !codeList.isEmpty();
	}

	public DataModel<InvoiceDealCode> getCodesModel() {
		return new ArrayDataModel(codeList.toArray());
	}

	public ArrayList<InvoiceDealCode> getCodesList() {
		return this.codeList;
	}
	
	public String doSetCodeUsed(InvoiceDealCode theCode, boolean codeUsed) {
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "UPDATE APP.DEAL_CODES SET code_used = "
						+ (codeUsed ? "1" : "0")
						+ " WHERE id = "
						+ theCode.getId();
				if (stmt.executeUpdate(sql) == 1) {
					FacesMessage msg = new FacesMessage();
					msg.setSeverity(FacesMessage.SEVERITY_INFO);
					msg.setSummary("Code \"" + theCode.getCode() + "\" is now used!");
					FacesContext.getCurrentInstance().addMessage(null, msg);
				} else {
					FacesMessage msg = new FacesMessage();
					msg.setSeverity(FacesMessage.SEVERITY_ERROR);
					msg.setSummary("Database Error: Invalid code!");
					FacesContext.getCurrentInstance().addMessage(null, msg);
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error");
			FacesContext.getCurrentInstance().addMessage(null, msg);
		}
		return null;
	}

	public void setDealListBean(DealListBean newValue) {
		this.dealListBean = newValue;
	}
}
